<?php

class Attendance extends Zend_Db_Table
{
    protected $_name = 'attendance';
    
    public function checkExist($lesson_id,$user_id)
    {
        $select = $this->getAdapter();
        $prepare = $select->query('
                SELECT count(*) as all_records 
                FROM attendance 
                WHERE lesson_id="'.$lesson_id.'"
                AND user_id="'.$user_id.'"');
        $result = $prepare->fetchAll(Zend_Db::FETCH_ASSOC);
        return $result[0]['all_records'];
    }
    
    public function getLessonsByUser($user_id)
    {
        $select = $this->getAdapter();
        $prepare = $select->query('
                SELECT lesson_id
                FROM attendance 
                WHERE user_id="'.$user_id.'"');
        $result = $prepare->fetchAll(Zend_Db::FETCH_ASSOC);
        $return = array();
        foreach($result as $r){
            $return[] = $r['lesson_id'];
        }
        return $return;
    }
    
    public function getLessons($abs)
    {
        $select = $this->getAdapter();
        $query = '
                SELECT lesson.*
                FROM lesson 
                WHERE id IN('.implode(',',$abs).')';
        //echo $query; exit();
        $prepare = $select->query($query);
        $result = $prepare->fetchAll(Zend_Db::FETCH_OBJ);
        $return = array();
        foreach($result as $r){
            echo $r->studygroup_id.'<br>';
            $return[$r->studygroup_id][] = $r;
        }
        return $return;
    }
    
    public function ClearLinks($lesson_id)
    {
        $select = $this->getAdapter();
        $prepare = $select->query('DELETE FROM attendance 
                                   WHERE lesson_id="'.(int)$lesson_id.'"');
    }
    
    public function getLinkedUser($lesson_id,$group_id)
    {
        $select = $this->getAdapter();
        $query = 'SELECT id,first_name,last_name,email,phone, max(presence) as pr
                                   FROM 
                                   (SELECT 
                                        user.id,
                                        user.first_name,
                                        user.last_name,
                                        user.email,
                                        user.phone,
                                        0 as presence
                                   FROM user
                                   INNER JOIN userstudygroup on userstudygroup.user_id = user.id
                                   and studygroup_id="'.(int)$group_id.'"
                                   
                                   UNION 
                                   
                                   SELECT 
                                        user.id,
                                        user.first_name,
                                        user.last_name,
                                        user.email,
                                        user.phone,
                                        1 as presence
                                   FROM user
                                   INNER JOIN attendance on attendance.user_id = user.id
                                   and lesson_id="'.(int)$lesson_id.'"
                                   ) s
                                   GROUP BY id
                                   ORDER BY last_name ';
        //echo '<pre>'.$query.'</pre>';exit();
        $prepare = $select->query($query);
        $result = $prepare->fetchAll();
        return $result;
    }
    
    public function getGroupAttendance($group_id)
    {
        $select = $this->getAdapter();
        $query = 'SELECT lesson.*,attendance.user_id
                  FROM lesson
                  INNER JOIN attendance on lesson.id = attendance.lesson_id
                  WHERE studygroup_id='.(int)$group_id.'
                  ORDER BY start_date,start_time';
        //echo '<pre>'.$query.'</pre>';exit();
        $prepare = $select->query($query);
        $result = $prepare->fetchAll();
        return $result;
    }
    
    public function LinkUser($lesson_id, $user_id)
    {
        $data = array(
                    'lesson_id' => $lesson_id,
                    'user_id' => $user_id);
        $this->insert($data);
    }
    
    public function UnlinkUser($lesson_id, $user_id)
    {
        $this->delete('lesson_id = '.$lesson_id.' and user_id = '.$user_id);
    }
    
    public function getUserLessons($student,$start,$end){
        $select = $this->getAdapter();
        $query = 'SELECT count(lesson.id) as count_lesson
                  FROM lesson
                  INNER JOIN attendance on lesson.id = attendance.lesson_id
                  AND attendance.user_id='.$student.'
                  WHERE start_date >= "'.$start.'"
                  AND start_date <= "'.$end.'"';
        $prepare = $select->query($query);
        $result = $prepare->fetchAll(Zend_Db::FETCH_OBJ);
        return $result[0]->count_lesson;
    }   
}